package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.erp.ErpGoodsStockInfoItemEntity;
import com.b2c.entity.ErpStocktakingInvoice;
import com.b2c.entity.ErpStocktakingInvoiceItem;
import com.b2c.entity.erp.enums.ErpStocktakingInvoiceStatusEnum;
import com.b2c.repository.Tables;
import com.b2c.repository.utils.OrderNumberUtils;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class ErpStocktakingInvoiceRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 分页获取
     * @param pageIndex
     * @param pageSize
     * @param No
     * @return
     */
    @Transactional
    public PagingResponse<ErpStocktakingInvoice>  getList(Integer pageIndex, Integer pageSize, String No) {
        String sql = "SELECT inv.*,sl.name as locationName,(SELECT COUNT(0) FROM "+Tables.ErpStocktakingInvoiceItem+" where iid = inv.id) as total FROM "+Tables.ErpStocktakingInvoice + " as inv left join " +Tables.ErpStockLocation+" as sl on sl.id = inv.locationId "+
                " WHERE 1=1 ";
        List<Object> params = new ArrayList<>();
        if(StringUtils.isEmpty(No)==false){
            sql += " AND inv.billNo = ? ";
            params.add(No);
        }
        sql += " ORDER BY id desc ";
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ErpStocktakingInvoice.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    public ErpStocktakingInvoice getInvoiceById(Long id) {
        try {
            String sql = "SELECT * FROM " + Tables.ErpStocktakingInvoice + " WHERE id=? ";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpStocktakingInvoice.class), id);
        }catch (Exception e){
            return null;
        }
    }

    public List<ErpStocktakingInvoiceItem> getInvoiceItemByIid(Long iid) {
        try {
            String sql = "SELECT it.*,gs.color_value,gs.size_value,sl.number as locationName FROM " + Tables.ErpStocktakingInvoiceItem + " it " +
                    " left join " +Tables.ErpGoodsSpec+" as gs on gs.id = it.goods_spec_id "+
                    " left join " +Tables.ErpStockLocation+" as sl on sl.id = it.locationId "+
                    " WHERE it.iid=? ";
            return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ErpStocktakingInvoiceItem.class), iid);
        }catch (Exception e){
            return null;
        }
    }

    /**
     * 添加盘点单
     * @param billNo
     * @param billDate
     * @param stockLocationId
     * @param userId
     * @param userName
     * @param invoiceItems
     */
    @Transactional
    public void addInvoice(String billNo, String billDate, Long stockLocationId, Integer userId, String userName, List<ErpStocktakingInvoiceItem> invoiceItems) {
        try {
            /***
             * 添加盘点单主表，默认就是待盘点录入状态
             */
            String sql = "INSERT INTO " + Tables.ErpStocktakingInvoice + " (billNo,billDate,userId,userName,locationId,createTime,status) VALUE (?,?,?,?,?,?,?)";
//        jdbcTemplate.update(sql,billNo,billDate,userId,userName,stockLocationId,System.currentTimeMillis() /1000 ,1);
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, billNo);
                    ps.setObject(2, billDate);
                    ps.setInt(3, userId);
                    ps.setString(4, userName);
                    ps.setLong(5, stockLocationId);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setInt(7, ErpStocktakingInvoiceStatusEnum.WaitCountedConfirm.getIndex());
                    return ps;
                }
            }, keyHolder);

            Long invoiceId = keyHolder.getKey().longValue();
            /**
             *
             * 添加盘点单子表信息
             */
            String itemSQL = "INSERT INTO " + Tables.ErpStocktakingInvoiceItem + " (iid,billNo,goods_id,goods_number,goods_spec_id,goods_spec_number,locationId,currentQty,countedQty,createTime) VALUE (?,?,?,?,?,?,?,?,?,?)";
            for (var item : invoiceItems) {
                jdbcTemplate.update(itemSQL, invoiceId, billNo, item.getGoods_id(), item.getGoods_number(), item.getGoods_spec_id(), item.getGoods_spec_number(), item.getLocationId(), item.getCurrentQty(),item.getCountedQty(),System.currentTimeMillis() / 1000);

            }
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
        }
    }

    /**
     * 盘点录入
     * @param invoiceId
     * @param userId
     * @param userName
     * @param invoiceItems
     */
    @Transactional
    public void countedInvoiceItem(Long invoiceId, Integer userId, String userName, List<ErpStocktakingInvoiceItem> invoiceItems) {
        try {
            var invoice = getInvoiceById(invoiceId);
            if (invoice == null) return;
            if (invoice.getStatus() != ErpStocktakingInvoiceStatusEnum.WaitCounted.getIndex()) return;

            //查询items
            //var items = getInvoiceItemByIid(invoiceId);

            /***更新盘点数据、更新库存数据***/
            String itemSQL = "UPDATE " + Tables.ErpStocktakingInvoiceItem + " SET countedQty=?,modifyTime=? WHERE id=?";

            //库存变动日志
            String logs = "INSERT INTO "+Tables.ErpGoodsStockLogs+" (goodsId,goodsNumber,specId,specNumber,locationId,quantity,type,sourceType,sourceId,remark,createOn,createUserId,createUserName) " +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";

            //更新当前库存
            String specCurrQty = "UPDATE erp_goods_spec SET currentQty = (SELECT SUM(currentQty) FROM erp_goods_stock_info WHERE specId=? AND isDelete=0) WHERE Id=?";

            for(var item: invoiceItems){
                /*******更新盘点item录入的数量********/
                jdbcTemplate.update(itemSQL,item.getCountedQty(),System.currentTimeMillis() /1000,item.getId());
            }

            //for (var item: items) {
                //查询盘点确认的数量
                //var countedItem = invoiceItems.stream().filter(p->p.getId() == item.getId()).findFirst().get();
                //if(countedItem!=null){

                    /*******更新盘点item录入的数量********/
                    //jdbcTemplate.update(itemSQL,countedItem.getCountedQty(),System.currentTimeMillis() /1000,countedItem.getId());

//                    /***********更新仓位库存************/
//                    jdbcTemplate.update("UPDATE "+Tables.ErpGoodsStockInfo+" SET currentQty=? WHERE specId=? AND locationId=? ",countedItem.getCountedQty(),item.getGoods_spec_id(),item.getLocationId());
//
//                    /***********添加仓位库存盘点记录************/
//                    jdbcTemplate.update(logs,item.getGoods_id(),item.getGoods_number(),item.getGoods_spec_id(),item.getGoods_spec_number(),item.getLocationId(),countedItem.getCountedQty(),
//                            3, EnumGoodsStockLogSourceType.COUNTED.getIndex(),item.getId(),"盘点更正库存数量,盘点单号："+invoice.getBillNo(),System.currentTimeMillis() / 1000,userId,userName);
//
//                    /****更新 商品规格 当前库存****/
//                    jdbcTemplate.update(specCurrQty,item.getGoods_spec_id(),item.getGoods_spec_id());
               // }
           // }




            /****更新盘点状态****/
            String sql = "UPDATE " + Tables.ErpStocktakingInvoice + " SET status=?,modifyTime=?,countedUserId=?,countedUserName=? WHERE id=?";
            jdbcTemplate.update(sql, ErpStocktakingInvoiceStatusEnum.WaitCountedConfirm.getIndex(), System.currentTimeMillis() / 1000, userId, userName, invoiceId);

        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
        }
    }


    /**
     * 盘点确认
     * @param invoiceId
     * @param userId
     * @param userName
     */
    @Transactional
    public void countedConfirm(Long invoiceId, Integer userId, String userName) {

        try {
            var invoice = getInvoiceById(invoiceId);
            if (invoice == null) return;
            if (invoice.getStatus() != ErpStocktakingInvoiceStatusEnum.WaitCountedConfirm.getIndex()) return;

            //查询items
            var items = getInvoiceItemByIid(invoiceId);


            //库存变动日志
            String logs = "INSERT INTO "+Tables.ErpGoodsStockLogs+" (goodsId,goodsNumber,specId,specNumber,locationId,quantity,type,sourceType,sourceId,remark,createOn,createUserId,createUserName,createTime,currQty) " +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            //更新当前库存
            String specCurrQty = "UPDATE erp_goods_spec SET currentQty = (SELECT SUM(currentQty) FROM erp_goods_stock_info WHERE specId=? AND isDelete=0) WHERE Id=?";


            //盘盈列表
            List<ErpStocktakingInvoiceItem> panYingList = new ArrayList<>();

            //盘亏列表
            List<ErpStocktakingInvoiceItem> panKuiList = new ArrayList<>();

            for (var item: items) {
                //判断    盘盈还是盘亏   盘点库存-当前库存  正数是盘盈  负数是盘亏
                long yingkui = item.getCountedQty().longValue() - item.getCurrentQty().longValue() ;

                /***********更新仓位库存************/
                jdbcTemplate.update("UPDATE "+Tables.ErpGoodsStockInfo+" SET currentQty=? WHERE specId=? AND locationId=? ",item.getCountedQty(),item.getGoods_spec_id(),item.getLocationId());


                /***********添加仓位库存盘点记录************/
                jdbcTemplate.update(logs,item.getGoods_id(),item.getGoods_number(),item.getGoods_spec_id(),item.getGoods_spec_number(),item.getLocationId(),yingkui,
                        3, EnumGoodsStockLogSourceType.COUNTED.getIndex(),item.getId(),"盘点更正库存数量,盘点单号："+invoice.getBillNo(),System.currentTimeMillis() / 1000,userId,userName,new Date(),item.getCountedQty());

                /****更新 商品规格 当前库存****/
                jdbcTemplate.update(specCurrQty,item.getGoods_spec_id(),item.getGoods_spec_id());


                /***************盘盈加入库记录*****************/
                if(yingkui > 0){
                    panYingList.add(item);
                }

                /***************盘亏加出库记录*****************/
                if(yingkui < 0){
                    panKuiList.add(item);
                }

            }


            /****更新盘点状态****/
            String sql = "UPDATE " + Tables.ErpStocktakingInvoice + " SET status=?,modifyTime=?,confirmTime=?,confirmUserId=?,confirmUserName=? WHERE id=?";
            jdbcTemplate.update(sql, ErpStocktakingInvoiceStatusEnum.SUCCESS.getIndex(), System.currentTimeMillis() / 1000,System.currentTimeMillis() / 1000, userId, userName, invoiceId);


            /******************************处理盘盈盘亏记录*******************************/
            if(panYingList.size() > 0){
                /********************         5、 添加入库单信息       ***********************/

                String sql1 = "INSERT INTO " + Tables.ErpStockInForm + " (number,checkoutId,isDelete,stockInUserId,stockInUserName,invoiceId,stockInTime1,inType) VALUE (?,?,0,?,?,?,?,?)";
                String formNum = "IN-PAN" + OrderNumberUtils.getOrderIdByMinute();//入库单号

                KeyHolder keyHolder = new GeneratedKeyHolder();

                jdbcTemplate.update(connection -> {
                    PreparedStatement ps = connection.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, formNum);
                    ps.setLong(2, 0);
                    ps.setInt(3, userId);
                    ps.setString(4, userName);
                    ps.setLong(5, invoiceId);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setInt(7, 3);//入库类型1采购入库2退货入库3盘点入库
                    return ps;
                }, keyHolder);
                //入库单id
                Long formId = keyHolder.getKey().longValue();


                for (var item : panYingList) {
                    var stockInfo = jdbcTemplate.queryForObject("SELECT * FROM erp_goods_stock_info WHERE isDelete=0 AND specId=? AND locationId=?",new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),item.getGoods_spec_id(),item.getLocationId());

                    long benciKuchun = item.getCountedQty().longValue() -item.getCurrentQty().longValue();
                    /***************   5.2 添加入库单明细数据 erp_stock_in_form_item***************/
                    jdbcTemplate.update("INSERT INTO " + Tables.ErpStockInFormItem + " (formId,goodsId,specId,quantity,locationId,itemId) VALUE (?,?,?,?,?,?)", formId
                            , item.getGoods_id(), item.getGoods_spec_id()
                            , benciKuchun
                            , item.getLocationId(),item.getId());

                    var goodsStockInfoItems2 = jdbcTemplate.query("SELECT * FROM erp_goods_stock_info_item WHERE stockInfoId=? ORDER BY id desc LIMIT 1 ", new BeanPropertyRowMapper<>(ErpGoodsStockInfoItemEntity.class),stockInfo.getId());
                    if(goodsStockInfoItems2.size()>0){
                        var stockInfo2=goodsStockInfoItems2.get(0);
                        jdbcTemplate.update("UPDATE erp_goods_stock_info_item SET currentQty=currentQty+? WHERE id=? ",benciKuchun,stockInfo2.getId());
                    }
            /*        *//***************   5.3 添加入库批次信息 erp_goods_stock_info_item***************//*
                    jdbcTemplate.update("INSERT INTO erp_goods_stock_info_item (stockInfoId,currentQty,purPrice,invoiceId,invoiceInfoId,stockInFormId,remark) value (?,?,0,0,0,?,?)"
                    ,stockInfo.getId(),benciKuchun,formId,"盘点新增批次");*/
                }
            }

            if(panKuiList.size() > 0){
                /*********添加出库数据*erp_stock_out_form*********/
                String stockOutFormSQL = "INSERT INTO "+Tables.ErpStockOutForm + " (stockOutNo,status,printStatus,printTime,createTime,createBy,modifyTime,completeTime,stockOutUserId,stockOutUserName,outType) " +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?)";

                String outNum = "OUT-PAN"+OrderNumberUtils.getOrderIdByMinute();
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(stockOutFormSQL, Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, outNum);
                        ps.setInt(2, 3);
                        ps.setInt(3, 0);
                        ps.setLong(4, 0);
                        ps.setLong(5, System.currentTimeMillis()/1000);
                        ps.setString(6, userName);
                        ps.setLong(7,System.currentTimeMillis()/1000);
                        ps.setLong(8, System.currentTimeMillis()/1000);
                        ps.setInt(9, userId);
                        ps.setString(10, userName);
                        ps.setInt(11, 3);//出库类型1订单拣货出库2采购退货出库3盘点出库
                        return ps;
                    }
                }, keyHolder);

                Long outFormId = keyHolder.getKey().longValue();

                for (var item : panKuiList) {
                    //库存变更数量
                    long kcbg = item.getCurrentQty().longValue() - item.getCountedQty().longValue();
                    //库存信息
                    var stockInfo = jdbcTemplate.queryForObject("SELECT * FROM erp_goods_stock_info WHERE isDelete=0 AND specId=? AND locationId=?",new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),item.getGoods_spec_id(),item.getLocationId());

                    var goodsStockInfoItems = jdbcTemplate.query("SELECT * FROM erp_goods_stock_info_item WHERE stockInfoId=? and currentQty>0 ", new BeanPropertyRowMapper<>(ErpGoodsStockInfoItemEntity.class), stockInfo.getId());
                    /**********处理先进先出问题**********/
                    if (goodsStockInfoItems.size() > 0) {
                        Long quantity = kcbg;//出库数量
                        ErpGoodsStockInfoItemEntity stockInfoItem = goodsStockInfoItems.get(0);
                        //数量小于库存 库存-数量
                        if (stockInfoItem.getCurrentQty() >= quantity) {
                            //更新goodsStockInfoItem
                            jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", quantity, stockInfoItem.getId());
                        } else {
                            Long cutQty = 0L;//已减库存
                            for (var stockItem : goodsStockInfoItems) {
                                cutQty += stockItem.getCurrentQty();
                                if (quantity > cutQty) {
                                    jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", stockItem.getCurrentQty(), stockItem.getId());
                                } else {
                                    Long cutQty_ = quantity - (cutQty - stockItem.getCurrentQty());
                                    jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", cutQty_, stockItem.getId());
                                    break;
                                }
                            }
                        }
                    }
                    /*********添加出库数据*erp_stock_out_form_item*********/
                    String stockOutFormItemSQL = "INSERT INTO erp_stock_out_form_item (formId,itemId,goodsId,specId,locationId,quantity,completeTime,status) value (?,?,?,?,?,?,?,?)";
                    KeyHolder keyHolderItem = new GeneratedKeyHolder();
                    jdbcTemplate.update(new PreparedStatementCreator() {
                        @Override
                        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                            PreparedStatement ps = connection.prepareStatement(stockOutFormItemSQL, Statement.RETURN_GENERATED_KEYS);
                            ps.setLong(1, outFormId);
                            ps.setLong(2, item.getId());
                            ps.setInt(3, item.getGoods_id().intValue());
                            ps.setInt(4, item.getGoods_spec_id().intValue());
                            ps.setInt(5, item.getLocationId().intValue());
                            ps.setLong(6, kcbg);
                            ps.setLong(7,System.currentTimeMillis()/1000);
                            ps.setInt(8, 3);
                            return ps;
                        }
                    }, keyHolderItem);
                    long stock_out_form_item_id = keyHolderItem.getKey().longValue();
//                    jdbcTemplate.update(stockOutFormItemSQL,outFormId,item.getId(),item.getGoods_id(),item.getGoods_spec_id(),item.getLocationId()
//                            ,kcbg,
//                            System.currentTimeMillis() / 1000 ,3);
                    //添加出库批次数据
                    jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)",
                            stock_out_form_item_id,stockInfo.getId(),0,kcbg);
                }
            }
//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
            throw e;
        }
    }
}
